using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Net;
using System.Threading;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Diagnostics;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.StorageClient;
using WorldBank;
using System.Data;
using System.Data.SqlClient;
using System.Data.Services.Client;

namespace Worker
{
    public class WorkerRole : RoleEntryPoint
    {
        private ElpurWorkerData _worker;

        public override void Run()
        {
            // This is a sample worker implementation. Replace with your logic.
            Trace.WriteLine("Worker entry point called", "Information");

            while (true)
            {
                _worker.UpdateLocalDatabase();
                Thread.Sleep(TimeSpan.FromHours(24));
                Trace.WriteLine("Working", "Information");
            }
        }

        public override bool OnStart()
        {
            _worker = new ElpurWorkerData();

            // Set the maximum number of concurrent connections 
            ServicePointManager.DefaultConnectionLimit = 12;

            // For information on handling configuration changes
            // see the MSDN topic at http://go.microsoft.com/fwlink/?LinkId=166357.

            return base.OnStart();
        }
    }

    public class ElpurWorkerData
    {
        private const string USER_ID = "aikeremu@live.com";
        private const string SECURE_ACCOUNT_ID = "9jUdsMDio/T9qgXROmWXVQUnfN5WNEptdhXEdftWFH4=";
        private const string ROOT_SERVICE_URL = "https://api.datamarket.azure.com/WorldBank/WorldDevelopmentIndicators/v1/";

        private const string DB_USERNAME = "elpurdb@xmapfxxhmm";
        private const string DB_PASSWORD = SECURE_ACCOUNT_ID;
        private const string DB_DATASOURCE = "tcp:xmapfxxhmm.database.windows.net,1433";
        private const string DB_NAME="Elpur_DB";

        private Uri serviceUri;
        private WorldDevelopmentIndicatorsContainer context;

        public ElpurWorkerData()
        {
            serviceUri = new Uri(ROOT_SERVICE_URL);
            context = new WorldDevelopmentIndicatorsContainer(serviceUri);
            context.IgnoreMissingProperties = true;
            context.Credentials = new NetworkCredential(USER_ID,
                SECURE_ACCOUNT_ID);
        }

        public void UpdateLocalDatabase()
        {
            SqlConnectionStringBuilder _ConnStringBuilder = new SqlConnectionStringBuilder();
                _ConnStringBuilder.DataSource = DB_DATASOURCE;
                _ConnStringBuilder.InitialCatalog = DB_NAME;
                _ConnStringBuilder.Encrypt = true;
                _ConnStringBuilder.TrustServerCertificate = false;
                _ConnStringBuilder.UserID = DB_USERNAME;
                _ConnStringBuilder.Password = DB_PASSWORD;

                using (SqlConnection conn = new SqlConnection(_ConnStringBuilder.ToString()))
                {
                    using (SqlCommand cmd= conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandText = "SELECT * FROM GetCountries";
                        SqlDataReader reader = null;
                        try
                        {
                            IEnumerator<CountryEntity> countries = context.GetCountries("en").GetEnumerator();
                            reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                countries.MoveNext();
                                string income = reader.GetString(4);
                                if (reader.FieldCount < 10)
                                    throw new Exception("Local database might corrupted");
                                if (income != countries.Current.IncomeLevelName)
                                {
                                    throw new Exception("Database update detected!");
                                }
                            }

                            if (countries.MoveNext())
                                throw new Exception("More data on the remote server.");
                        }
                        catch (Exception e)
                        {
                            if (reader != null)
                                reader.Close();
                            Trace.WriteLine(e.Message);
                            RefreshAll(conn);
                        }
                        if (reader != null)
                            reader.Close();
                        conn.Close();
                    }
                }
        }


        private void RefreshAll(SqlConnection conn)
        {
            using (SqlCommand cmd_ref = conn.CreateCommand())
            {
                cmd_ref.CommandText = "DROP TABLE GetCountries";
                cmd_ref.ExecuteNonQuery();
                cmd_ref.CommandText = "CREATE TABLE GetCountries (Number int NOT NULL, CapitalCity varchar(100), CountryName varchar(100), "
                + "CodeIso3Letter varchar(10), IncomeLevelName varchar(20), RegionCode varchar(20), RegionName varchar(100), "
                + "LendingTypeCode varchar(10), LendingTypeName varchar(20), "
                + "Latitude float, Longitude float)";
                cmd_ref.ExecuteNonQuery();
                cmd_ref.CommandText = "CREATE CLUSTERED INDEX Index_GetCountries ON GetCountries(Number)";
                cmd_ref.ExecuteNonQuery();

                int _Index = 1;

                foreach (CountryEntity country in context.GetCountries("en"))
                {
                    cmd_ref.CommandText = "INSERT INTO GetCountries (Number,CapitalCity,CountryName,CodeIso3Letter,IncomeLevelName,RegionCode"
                    + ",Latitude,Longitude,RegionName,LendingTypeCode,LendingTypeName) VALUES ("
                        + _Index + ",'"
                        + country.CapitalCity.Replace("'", "''")
                        + "','" + country.Name.Replace("'", "''")
                        + "','" + country.CodeIso3Letter.Replace("'", "''")
                        + "','" + country.IncomeLevelName.Replace("'", "''")
                        + "','" + country.RegionCode.Replace("'", "''")
                        + "','" + country.Latitude
                        + "','" + country.Longitude
                        + "','" + country.RegionName.Replace("'", "''")
                        + "','" + country.LendingTypeCode.Replace("'", "''")
                        + "','" + country.LendingTypeName.Replace("'", "''")
                        + "')";
                    cmd_ref.ExecuteNonQuery();
                    _Index++;
                }
            }
        }
    }
}
